package com.webactus.appli;

import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.sax.StartElementListener;

public class WebActusDBAdapter {

	private static final String ARTICLES_TABLE = "articles";
	private static final String DATABASE_NAME = "webActusDB.db";
	private static final int DATABASE_VERSION = 8;
	
	private SQLiteDatabase db;
	private final Context context;
	private WebActusDBHelper webActusDBHelper;
	
	public static final String KEY_ID = "_id";
	
	public static final String KEY_TITLE = "title";
	public static final int TITLE_COLUMN = 1;
	public static final String KEY_URL = "url";
	public static final int URL_COLUMN = 2;
	public static final String KEY_IMG = "img";
	public static final int IMG_COLUMN = 3;
	public static final String KEY_DESCRIPTION = "decription";
	public static final int DESCRIPTION_COLUMN = 4;
	
	String[] columns = new String[]{KEY_ID,KEY_TITLE,KEY_URL,KEY_IMG,KEY_DESCRIPTION};

	private static final String CREATE_TABLE_ARTICLES = "create table " +
			ARTICLES_TABLE + " ( "+KEY_ID+" integer primary key autoincrement,"+
			KEY_TITLE + " text not null,"+
			KEY_URL + " text not null,"+
			KEY_IMG + " text,"+
			KEY_DESCRIPTION + " text not null"+
			");";

	public WebActusDBAdapter(Context context){
		this.context = context;
		webActusDBHelper = new WebActusDBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public WebActusDBAdapter open(){
			try{
				db  = webActusDBHelper.getWritableDatabase();
			}catch(SQLException ex){
				db = webActusDBHelper.getReadableDatabase();
			}
			db.execSQL("DROP TABLE IF EXISTS "+ ARTICLES_TABLE);
			db.execSQL(CREATE_TABLE_ARTICLES);
		return this;
	}
	
	public void close(){
		db.close();
	}
	
	public void insertArticle(Article article){
		ContentValues values = new ContentValues();
		
		values.put(KEY_TITLE	, article.getTitle());
		values.put(KEY_URL, article.getUrl().toString());
		values.put(KEY_IMG,article.getImg());
		values.put(KEY_DESCRIPTION, article.getDescription());
		
		if(!db.isOpen()) open();
		db.insert(ARTICLES_TABLE, null, values);
	}
	
	public void updateArticle(Article article){
		String whereClause;
		ContentValues values = new ContentValues();
		
		whereClause = KEY_ID + " = "+article.get_id();
		values.put(KEY_TITLE	, article.getTitle());
		values.put(KEY_URL, article.getUrl().toString());
		values.put(KEY_IMG,article.getImg());
		values.put(KEY_DESCRIPTION, article.getDescription());
		
		db.update(ARTICLES_TABLE, values, whereClause, null);
	}
	
	public boolean removeArticle(int _rowIndex){
		return db.delete(ARTICLES_TABLE, KEY_ID + " = "+_rowIndex , null) > 0;
	}
	
	public List<Article> getAllArticles(){
		List<Article> articles = new ArrayList<Article>();
		
		Cursor c = db.query(ARTICLES_TABLE,columns ,null, null, null, null, null);
		while(c.moveToNext()){
			Article article = new Article();
			article.set_id(c.getInt(c.getColumnIndexOrThrow(KEY_ID)));
			article.setTitle(c.getString(c.getColumnIndexOrThrow(KEY_TITLE)));
			if(c.getColumnIndex(KEY_IMG) >  -1){
				article.setImg(c.getString(c.getColumnIndexOrThrow(KEY_IMG)));
			}
			article.setDescription(c.getString(c.getColumnIndexOrThrow(KEY_DESCRIPTION)));
			try {
				article.setUrl(new URL(c.getString(c.getColumnIndexOrThrow(KEY_URL))));
			} catch (MalformedURLException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			}
			articles.add(article);
		}
		return articles;
	}
	
	public Article getArticle(int _rowIndex){
		Cursor c = db.query(ARTICLES_TABLE, columns ,
				KEY_ID + " = "+ _rowIndex, null, null, null, null);
		c.moveToFirst();
		Article article = new Article();
		article.setTitle(c.getString(c.getColumnIndexOrThrow(KEY_TITLE)));
		article.set_id(c.getInt(c.getColumnIndexOrThrow(KEY_ID)));
		article.setImg(c.getString(c.getColumnIndexOrThrow(KEY_IMG)));
		article.setDescription(c.getString(c.getColumnIndexOrThrow(KEY_DESCRIPTION)));
		try {
			article.setUrl(new URL(c.getString(c.getColumnIndexOrThrow(KEY_URL))));
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		return article;
	}
	
	private static class WebActusDBHelper extends SQLiteOpenHelper{

		public WebActusDBHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_TABLE_ARTICLES);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS "+ ARTICLES_TABLE);
			onCreate(db);
		}
		
	}
	
	
	
}
